package nl.jjsoft.airlock
{
	import flash.data.SQLConnection;
	import flash.data.SQLMode;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.filesystem.File;
	import flash.utils.ByteArray;
	
	/**
	 * Wrapper for the flex SQL database methods.
	 * Uses a synchronous methods to call query the database.
	 * 
	 * Classes internally used: SQLConnection, SQLStatement, SQLResult and SQLError
	 */
	public class SqlDB
	{
		// see http://livedocs.adobe.com/flex/3/html/help.html?content=SQL_03.html
		private var sqlConnection:SQLConnection;
		private var sqlError:SQLError;
		
		private const ENCRYPTED_DB_PASSWORD_ERROR_ID:uint = 3138;
		
		/**
		 * Constructor for the SQL DB Wrapper
		 * 
		 * @param String dbFileName The filename for the database, will be stored in applicationStorageDirectory
		 * @param Boolean readonly [Optional]Open the database in readonly modus, default false
		 */
		public function SqlDB() {
			sqlConnection = new SQLConnection();
			sqlError = null;			
		}
		
		public function disconnect():void {			
			if (sqlConnection.connected){
				sqlConnection.close();				
			}
			sqlError = null;
		}
		
		public function connect(dbFile:File,encryptionKey:ByteArray = null, sqlMode:String = SQLMode.UPDATE):Boolean {
			sqlError = null;
			try {
				sqlConnection.open(dbFile, sqlMode, false, 1024, encryptionKey);
				return true;
			}
			catch (error:SQLError) {
				handleSQLError(error);
			}
			return false;
		}
		
		/**
		 * Indicates if the databases is connected
		 */
		public function get connected():Boolean {
			return sqlConnection.connected;
		}
		
		/**
		 * Indicates if a sql error occured in the last method call
		 */
		public function get hasSQLError():Boolean {
			return (sqlError != null);
		}
		
		/**
		 * Execute a SQL Query
		 * 
		 * @param String query The SQL query to perform on the database
		 * @param Object params [Optional] The parameters for the SQL query, default null
		 * @result Boolean Indicates if the query was succesfull
		 */ 
		public function execute(query:String, params:Object = null):Boolean {
			var statement:SQLStatement = createSQLStatement(query, params);
			sqlError = null;
			
			sqlConnection.begin();
			try {
				statement.execute();
				sqlConnection.commit();			
				return true;
			}
			catch (e:SQLError) {
				sqlConnection.rollback();
				handleSQLError(e);
			}
			
			return false;
		}
		
		/**
		 * Select data from the datavase using a SQL Query
		 * 
		 * @param String query The SQL query to perform on the database
		 * @param Object params [Optional] The parameters for the SQL query, default null
		 * @param Class itemClass [Optional] The class that the result array objects will be cast to, default null
		 * @result Array If succesfull an array containing the selected data, otherwise null
		 */ 
		public function select(query:String, params:Object = null, itemClass:Class = null):Array {
			var result:SQLResult = executeQueryAndGetSQLResult(query, params, itemClass);
			if (result != null){
				return result.data;
			}
			return null;
		}
		
		/**
		 * Execute a SQL INSERT Query
		 * 
		 * @param String query The SQL query to perform on the database
		 * @param Object params [Optional] The parameters for the SQL query, default null
		 * @result Boolean Indicates if the query was succesfull (row inserted)
		 */ 
		public function insert(query:String, params:Object = null):Number {
			var result:SQLResult = executeQueryAndGetSQLResult(query, params);
			if (result != null) {
				return result.lastInsertRowID;				
			}
			return -1;
		}
		
		/**
		 * Execute a SQL UPDATE Query
		 * 
		 * @param String query The SQL query to perform on the database
		 * @param Object params [Optional] The parameters for the SQL query, default null
		 * @result Boolean Indicates if the query was succesfull (row(s) updated)
		 */ 
		public function update(query:String, params:Object = null):Number {
			var result:SQLResult = executeQueryAndGetSQLResult(query, params);
			if (result != null) {
				return result.rowsAffected;				
			}
			return -1;
		}
		
		/**
		 * Execute a SQL DELETE Query
		 * 
		 * @param String query The SQL query to perform on the database
		 * @param Object params [Optional] The parameters for the SQL query, default null
		 * @result Boolean Indicates if the query was succesfull (row(s) deleted)
		 */ 
		public function remove(query:String, params:Object = null):Boolean {
			var result:SQLResult = executeQueryAndGetSQLResult(query, params);
			if (result != null) {
				return result.rowsAffected >= 1;				
			}
			return false;
		}
		
		private function handleSQLError(error:SQLError):void {
			sqlError = error;
			trace("Error message:", error.message);
			trace("Details:", error.details);
		}
		
		private function createSQLStatement(query:String, params:Object = null, itemClass:Class = null):SQLStatement {
			var statement:SQLStatement = new SQLStatement();
			statement.sqlConnection = sqlConnection;
			statement.text = query;
			
			if (params != null) {// if params are passed then fill them
				for (var i:String in params) {
					statement.parameters[i] = params[i];
				}
			}
			if (itemClass != null) {//if itemClass is defined the set it
				statement.itemClass = itemClass;
			}
			return statement;
		}
		
		private function executeQueryAndGetSQLResult(query:String, params:Object=null, itemClass:Class=null):SQLResult {
			var statement:SQLStatement = createSQLStatement(query, params, itemClass);
			sqlError = null;
			
			sqlConnection.begin();
			try {
				statement.execute();
				sqlConnection.commit();
				return statement.getResult();
			}
			catch (e:SQLError) {
				sqlConnection.rollback();
				handleSQLError(e);
			}
			return null;
		}
	}
}